package com.wardrobe.soa.utils;

import com.wardrobe.soa.common.ExcelDownMap;
import com.wardrobe.soa.common.ExcelUploadRow;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by niuyonggang
 */
public class ExcelFileHelper {

    private static Logger logger = LoggerFactory.getLogger(ExcelFileHelper.class);

    public static final int WRITE_BUFFER_SIZE = 1024;

    public static void writeExcelFileToClient(HttpServletResponse response, BufferedInputStream bufferedInputStream, String fileName,String ext) {
        String isoFilename = fileName;
        try {
            isoFilename = new String(fileName.getBytes("utf-8"),"ISO8859_1");
        } catch (UnsupportedEncodingException e) {
            logger.error("excelFile encoding failed.. :{} {}", fileName, StackTraceUtil.getStackTrace2(e));
            return;
        }
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        response.reset();
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Type","application/vnd.ms-excel");
        response.addHeader("Content-Disposition", "attachment; filename=\"" + isoFilename + "." + ext + "\"");

        try {
            writeExcelFileToOutputStream(response.getOutputStream(), bufferedInputStream);
        } catch (IOException e) {
            logger.error("getOutputStream error:", e);
        }
    }

    public static void writeExcelFileToOutputStream(OutputStream outputStream, BufferedInputStream bufferedInputStream) {
        byte[] b = new byte[WRITE_BUFFER_SIZE];
        int len;
        try {
            while ((len = bufferedInputStream.read(b)) > 0)
                outputStream.write(b, 0, len);
        } catch (IOException e) {
            logger.error("excelFile download error:", e);
        }finally {
            try {
                if(bufferedInputStream!=null) {
                    bufferedInputStream.close();
                }
            } catch (IOException e) {
                logger.error("InputStream.close error:", e);
            }
        }
    }

    public static BufferedInputStream createExcel(String sheetName, ExcelDownMap excelMap, List<Map<String,Object>> result){
        LinkedHashMap<String,String> titleMap = excelMap.sortedFieldMapBean();
        byte[] bytes = createExcelBytes(sheetName,titleMap,result);
        ByteArrayInputStream bufferedWriter = new ByteArrayInputStream(bytes);
        return new BufferedInputStream(bufferedWriter);
    }

    public static byte[] createExcelBytes(String sheetName, LinkedHashMap<String,String> title, List<Map<String,Object>> result){
        // 第一步，创建一个webbook，对应一个Excel文件
        XSSFWorkbook wb = new XSSFWorkbook();
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        XSSFSheet sheet = wb.createSheet(sheetName);
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        XSSFRow row = sheet.createRow(0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        XSSFCellStyle style = wb.createCellStyle();
        XSSFFont font = wb.createFont();
        font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);//粗体显示
        style.setFont(font);
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式


        //用来存放要展示列表的key 一定要所查数据的数据库列名一致对应
        XSSFCell cell = row.createCell(0);
        int cellIndex=0;
        for (String str : title.keySet()){
            String st = title.get(str);
            cell.setCellValue(st);
            cell.setCellStyle(style);
            sheet.setColumnWidth(cellIndex,st.getBytes().length*2*256);
            cellIndex++;
            cell = row.createCell(cellIndex);
        }

        // 第五步，写入实体数据 实际应用中这些数据从数据库得到
        if(CollectionUtils.isEmpty(result)){
            for (int i = 0; i < result.size(); i++) {
                //创建一行
                row = sheet.createRow(i + 1);
                int j=0;
                for(String str : title.keySet()){
                    // 第四步，创建单元格，并设置值
                    Object obj = result.get(i).get(str);
                    XSSFCell cellNew = row.createCell(j);
                    if(obj instanceof Integer){
                        cellNew.setCellValue(((Integer) obj).intValue());
                    }else if(obj instanceof Long){
                        cellNew.setCellValue(((Long) obj).longValue());
                    }else if(obj instanceof Float){
                        cellNew.setCellValue(((Float) obj).floatValue());
                    }else if(obj instanceof Double){
                        cellNew.setCellValue(((Double) obj).doubleValue());
                    }else if(obj instanceof Date){
                        cellNew.setCellValue((Date) obj);
                    }else if(obj instanceof BigDecimal){
                        cellNew.setCellValue(((BigDecimal) obj).doubleValue());
                    }else{
                        cellNew.setCellValue(obj == null ? "" : obj.toString());
                    }
                    j++;
                }
            }
            // 第六步，将文件存到指定位置
            ByteArrayOutputStream fout = null;
            try{
                fout = new ByteArrayOutputStream();
                wb.write(fout);
                return fout.toByteArray();
            }catch (Exception e){
                e.printStackTrace();
            }finally {
                if(fout != null){
                    try {
                        fout.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
                if(wb != null){
                    try {
                        wb.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
        }

        return null;
    }

    public static <T> List<T> resolveExcel(InputStream stream, ExcelUploadRow<List<T>> uploadRow){
        Workbook wb = null;
        try {
            wb = new XSSFWorkbook(stream);
            Sheet sheet = wb.getSheetAt(0);
            return uploadRow.handleResolveExcelRes(sheet);
        } catch (IOException e) {
            logger.error("resolveExcel.XSSFWorkbook error:", e);
        }
        return null;
    }

}
